package jp.co.dailyReport.total.sql;

import java.util.Calendar;
import java.util.List;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.apache.log4j.Logger;
import jp.co.dailyReport.Executer;
import jp.co.dailyReport.total.TotalHoursWorkedProjectCondition;

/**
 * プロジェクト毎稼働時間集計結果件数を取得するクラスです。
 */
public class TotalHoursWorkedProjectCountExecuter implements Executer {

	private static Logger log = Logger.getLogger(TotalHoursWorkedProjectCountExecuter.class);

	private StringBuffer sqlStatement;
	private List searchObjectList;
	private Integer projectId;
	private java.sql.Date startWorkDate;
	private java.sql.Date endWorkDate;

	public TotalHoursWorkedProjectCountExecuter(TotalHoursWorkedProjectCondition totalHoursWorkedProjectCondition) {
		searchObjectList = new ArrayList();
		sqlStatement = new StringBuffer();

		if(totalHoursWorkedProjectCondition.getProjectId()!=0) 
			setProjectId(totalHoursWorkedProjectCondition.getProjectId());
		if(totalHoursWorkedProjectCondition.getStartWorkDate()!=null) 
			setStartWorkDate(totalHoursWorkedProjectCondition.getStartWorkDate());
		if(totalHoursWorkedProjectCondition.getEndWorkDate()!=null) 
			setEndWorkDate(totalHoursWorkedProjectCondition.getEndWorkDate());
	}

	private void appendAND(String str){
		if(sqlStatement.length() > 0)
			sqlStatement.append(" AND " + str);
		else
			sqlStatement.append(str);
	}

	private void appendOR(String str){
		if(sqlStatement.length() > 0)
			sqlStatement.append(" OR " + str);
		else
			sqlStatement.append(str);
	}

	public void setProjectId(int projectId) {
		this.projectId = new Integer(projectId);
		searchObjectList.add(this.projectId);
		appendAND("p.projectId = ?");
	}

	public void setStartWorkDate(Calendar startWorkDate){
	  this.startWorkDate = new java.sql.Date(startWorkDate.getTime().getTime());
	  this.searchObjectList.add(this.startWorkDate);
	  appendAND("wr.workDateEnd >= ?");
	}

	public void setEndWorkDate(Calendar endWorkDate){
	  this.endWorkDate = new java.sql.Date(endWorkDate.getTime().getTime());
	  this.searchObjectList.add(this.endWorkDate);
	  appendAND("wr.workDateEnd <= ?");
	}

	/**
	 * 設定された条件を基に、プロジェクト毎稼働時間集計結果件数を取得します。
	 * 
	 * @param conn コネクションオブジェクト
	 * @return プロジェクト毎稼働時間集計結果件数
	 * @throws SQLException
	 */
	public Object[] execute(Connection conn) throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rset = null;
		Integer answer = null;

		String sql = "SELECT count(DISTINCT ww.weeklyReportWorkItemId) AS count " +
					 "FROM weeklyReport AS wr " +
					 "LEFT JOIN project AS p ON wr.projectId = p.projectId " +
					 "LEFT JOIN weeklyReportWorkItem AS ww ON wr.weeklyReportId = ww.weeklyReportId " +
					 "LEFT JOIN workItem AS wi ON ww.workItemId = wi.workItemId";

		if(sqlStatement.length() > 0){
			sql = sql + " where " + sqlStatement.toString();
		}
		log.debug("sql: " + sql);

		try {
			pstmt = conn.prepareStatement(sql);

			if(this.projectId != null){
				pstmt.setInt(this.searchObjectList.indexOf(this.projectId)+1, this.projectId.intValue());
				releaseSearchObject(this.projectId);
			}

			if(this.startWorkDate != null){
				pstmt.setDate(this.searchObjectList.indexOf(this.startWorkDate)+1, this.startWorkDate);
				releaseSearchObject(this.startWorkDate);
			}

			if(this.endWorkDate != null){
				pstmt.setDate(this.searchObjectList.indexOf(this.endWorkDate)+1, this.endWorkDate);
				releaseSearchObject(this.endWorkDate);
			}
			rset = pstmt.executeQuery();
			if (rset.next()) {
				answer = new Integer(rset.getInt("count"));
			} else {
				throw new SQLException("no data found");
			}
		} finally {
			if(rset != null)
				rset.close();
			if(pstmt != null)
				pstmt.close();
		}
		return new Integer[] { answer };
	}

	private void releaseSearchObject(Object obj){
		searchObjectList.set(searchObjectList.indexOf(obj), null);
	}
}

